﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Globalization;
using System.IO;
using System.Data;

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

using SpreadsheetLight;
using SpreadsheetLight.Drawing;

using BMS.Models;
using BMS.Models.DataAccess;

namespace BMS.Utilities
{
    public static class ExcelOpenXmlUtil
    {
        private static WorksheetPart GetWorksheetPartByName(SpreadsheetDocument document, string sheetName)
        {
            IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName);

            if (sheets.Count() == 0)
            {
                // The specified worksheet does not exist.
                return null;
            }

            string relationshipId = sheets.First().Id.Value;
            WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
            return worksheetPart;
        }

        private static string GetCellText(Cell cell, SharedStringTable strTable)
        {
            if (cell.ChildElements.Count == 0)
            {
                return null;
            }
            String val = cell.CellValue.InnerText;
            if (cell.DataType != null && cell.DataType == CellValues.SharedString)
            {
                val = strTable.ChildElements[int.Parse(val)].InnerText;
            }
            return val;
        }

        public static bool CreateQuotationOrInvoiceInExcelFormat(String tabName, ExcelModel data)
        {
            bool IsSuccess = false;
            FMRepository fmRepsoitory = new FMRepository();

            try
            {
                String srcPath = data.ExcelSrcPathForGeneralTemplate;

                String newTabName = data.Data.QuotationNo;
                String tempDestPath = data.ExcelDestPath + data.ExcelFileName;

                if (data.Data.IsQuotation == false && data.Data.IsInvoice == true)
                    newTabName = data.Data.InvoiceNo;

                // (1) Rename new tab name
                using (SLDocument doc = new SLDocument(srcPath, tabName))
                {
                    doc.RenameWorksheet(tabName, newTabName);
                    doc.SaveAs(tempDestPath);
                }

                // (2) Plug In Data into Excel
                if (File.Exists(tempDestPath) == true)
                {
                    if (data.Data.IsQuotation == true && data.Data.IsInvoice == false)
                    {
                        #region Quotation
                        using (SLDocument doc = new SLDocument(tempDestPath, newTabName))
                        {
                            int NoOfItems = data.Data.QuotationItemsList.Count;
                            int NoOfPages = (int)Math.Ceiling((double)NoOfItems / (double)data.MaxNoOfRecords);
                            int CurrentItemColumnNo = 16;
                            int NoOfRowsToBeAdded = (NoOfItems) + (16 * (NoOfPages - 1)) + (2 * NoOfPages);
                            doc.InsertRow(CurrentItemColumnNo + 2, NoOfRowsToBeAdded);
                            Decimal TotalAmount = 0;
                            decimal CarryForward = 0;
                            decimal BroughtForward = 0;

                            for (int j = 0; j < NoOfPages; j++)
                            {
                                int CurrentPage = j + 1;
                                int startAnchor = ((CurrentPage - 1) * 38) + 2;

                                int EndOfPageRow = CurrentPage * 40;
                                int EndOfPageColumn = 12;

                                if (CurrentPage >= 2 && CurrentPage <= NoOfPages)
                                {
                                    int tempEndRow = (j * 40);
                                    doc.CopyRow(1, CurrentItemColumnNo, startAnchor, false);
                                    SLPicture pic = new SLPicture(data.CompanyLogoPath);
                                    pic.ResizeInPixels(150, 56);
                                    pic.SetPosition((double)tempEndRow, 7.8);
                                    doc.InsertPicture(pic);
                                }

                                #region Billing Info
                                int tempRowNumber = (j * 39) + 4;
                                doc.SetCellValue("C" + tempRowNumber, data.Data.client.ClientName);
                                tempRowNumber = (j * 39) + 5;
                                doc.SetCellValue("C" + tempRowNumber, data.Data.client.Address1);
                                tempRowNumber = (j * 39) + 6;
                                doc.SetCellValue("C" + tempRowNumber, data.Data.client.Address2);
                                tempRowNumber = (j * 39) + 7;
                                doc.SetCellValue("C" + tempRowNumber, data.Data.client.Address3);
                                tempRowNumber = (j * 39) + 9;
                                doc.SetCellValue("C" + tempRowNumber, data.Data.client.AttentionTo);

                                String tempQuotationNo = data.Data.QuotationNo;
                                String[] QuoNoSplit = tempQuotationNo.Split('-');
                                String tempQuoNo = "";

                                for (int i = 1; i < QuoNoSplit.Length; i++)
                                {
                                    if (i != QuoNoSplit.Length - 1)
                                        tempQuoNo += QuoNoSplit[i] + "-";
                                    else
                                        tempQuoNo += QuoNoSplit[i];
                                }

                                String PageStrn = CurrentPage + " of " + NoOfPages;

                                if (data.Data.IsQuotation == true && data.Data.IsInvoice == false)
                                {
                                    tempRowNumber = (j * 39) + 4;
                                    doc.SetCellValue("J" + tempRowNumber, tempQuoNo);
                                    tempRowNumber = (j * 39) + 5;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.WorkOrderNo);
                                    tempRowNumber = (j * 39) + 6;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.QuotationDate);
                                    tempRowNumber = (j * 39) + 7;
                                    doc.SetCellValue("J" + tempRowNumber, PageStrn);
                                    tempRowNumber = (j * 39) + 8;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.ReferenceNo);
                                    tempRowNumber = (j * 39) + 9;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.VendorNo);
                                }
                                //else if (data.Data.IsQuotation == false && data.Data.IsInvoice == true)
                                //{
                                //    tempRowNumber = (j * 39) + 4;
                                //    doc.SetCellValue("J" + tempRowNumber, data.Data.InvoiceNo);
                                //    tempRowNumber = (j * 39) + 5;
                                //    doc.SetCellValue("J" + tempRowNumber, data.Data.QuotationNo);
                                //    tempRowNumber = (j * 39) + 6;
                                //    doc.SetCellValue("J" + tempRowNumber, data.Data.WorkOrderNo);
                                //    tempRowNumber = (j * 39) + 7;
                                //    doc.SetCellValue("J" + tempRowNumber, data.Data.InvoiceDate);
                                //    tempRowNumber = (j * 39) + 8;
                                //    doc.SetCellValue("J" + tempRowNumber, PageStrn);
                                //    tempRowNumber = (j * 39) + 9;
                                //    doc.SetCellValue("J" + tempRowNumber, data.Data.ReferenceNo);
                                //    tempRowNumber = (j * 39) + 10;
                                //    doc.SetCellValue("J" + tempRowNumber, data.Data.VendorNo);
                                //}
                                #endregion

                                #region Service Address
                                int tempSANo = 13;

                                if (data.Data.IsQuotation == true)
                                    tempSANo = 12;

                                tempRowNumber = (j * 39) + tempSANo;

                                String tStrn = data.Data.Location + " - " + data.Data.ServiceDetail;

                                if (data.Data.ServiceDetail == "" || data.Data.ServiceDetail == null)
                                    tStrn = data.Data.Location;

                                doc.SetCellValue("D" + tempRowNumber, tStrn);
                                #endregion

                                #region Items List & Work Completion Date
                                String WorkCompletionDateCellRange = "A20";
                                int WorkCompletionDateColumnNo = 20;
                                int TotalAmountColumnNo = 18;
                                int InvoiceWordColumnNo = 23;

                                //if (data.Data.IsQuotation == true)
                                //{
                                //    WorkCompletionDateCellRange = "A19";
                                //    WorkCompletionDateColumnNo = 19;
                                //    TotalAmountColumnNo = 18;
                                //    InvoiceWordColumnNo = 22;
                                //}

                                // (b) Find out the number of rows to be added
                                #region Rows Calculation
                                tempRowNumber = (j * 38) + CurrentItemColumnNo;
                                SLStyle style = doc.GetCellStyle("J" + tempRowNumber);
                                style.FormatCode = "#,##0.00";
                                doc.SetCellStyle("J" + tempRowNumber, style);

                                WorkCompletionDateColumnNo += NoOfRowsToBeAdded;
                                InvoiceWordColumnNo += NoOfRowsToBeAdded;
                                WorkCompletionDateCellRange = "A" + WorkCompletionDateColumnNo;


                                int tempEnd = (CurrentPage * CurrentItemColumnNo) + 21;
                                if (CurrentPage == NoOfPages)
                                {
                                    tempEnd = (j * 40) + CurrentItemColumnNo + (NoOfItems % data.MaxNoOfRecords) + 1;
                                }

                                if (CurrentPage == 1)
                                {
                                    if (data.Data.IsQuotation == true)
                                        tempRowNumber = CurrentItemColumnNo + 1;
                                    else
                                        tempRowNumber = CurrentItemColumnNo + 2;
                                }
                                else
                                {
                                    if (data.Data.IsQuotation == true)
                                        tempRowNumber = (j * 40) + (CurrentItemColumnNo - 1);
                                    else
                                        tempRowNumber = (j * 40) + CurrentItemColumnNo;
                                }

                                for (int i = tempRowNumber; i < tempEnd; i++)
                                {
                                    int tempRowNo = CurrentItemColumnNo + 1;
                                    style = doc.GetCellStyle("A" + tempRowNo);
                                    doc.SetCellStyle("A" + i, style);
                                    style = doc.GetCellStyle("B" + tempRowNo);
                                    doc.SetCellStyle("B" + i, style);
                                    doc.MergeWorksheetCells("B" + i, "E" + i);
                                    style = doc.GetCellStyle("F" + tempRowNo);
                                    doc.SetCellStyle("F" + i, style);
                                    style = doc.GetCellStyle("H" + tempRowNo);
                                    doc.SetCellStyle("H" + i, style);
                                    style = doc.GetCellStyle("I" + tempRowNo);
                                    doc.SetCellStyle("I" + i, style);
                                    style = doc.GetCellStyle("J" + tempRowNo);
                                    style.FormatCode = "#,##0.00";
                                    doc.SetCellStyle("J" + i, style);
                                    style = doc.GetCellStyle("K" + tempRowNo);
                                    style.FormatCode = "#,##0.00";
                                    doc.SetCellStyle("K" + i, style);
                                }
                                #endregion

                                String tempStrn = "";
                                int SIndex = GetStartIndex(data, j); ;
                                int EIndex = GetEndIndex(data, CurrentPage, NoOfPages);
                                int tNo = tempRowNumber;
                                if (CurrentPage == 1)
                                    tNo = tNo - 1;

                                #region Items Update
                                if (CurrentPage >= 2 && CurrentPage <= NoOfPages)
                                {
                                    tNo = tNo - 1;
                                    doc.SetCellValue("B" + tNo, "");
                                    doc.MergeWorksheetCells("I" + tNo, "J" + tNo);
                                    doc.SetCellValue("I" + tNo, "Brought Forward:");
                                    style = doc.GetCellStyle("K16");
                                    style.FormatCode = "#,##0.00";
                                    style.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
                                    style.Border.BottomBorder.Color = System.Drawing.Color.Black;
                                    doc.SetCellStyle("K" + tNo, style);
                                    doc.SetCellValue("K" + tNo, BroughtForward);
                                    tNo += 1;
                                }

                                int tempIndex = 0;

                                if (data.Data.IsQuotation == true)
                                {
                                    #region JLLOrder Items
                                    for (int i = SIndex, m = tNo; i < EIndex && m < tempEnd; i++, m++)
                                    {
                                        tempStrn = (data.Data.QuotationItemsList[i].ItemNo == 0) ? "" : data.Data.QuotationItemsList[i].ItemNo.ToString("00") + ".";
                                        doc.SetCellValue("A" + m, tempStrn);
                                        doc.SetCellValue("B" + m, data.Data.QuotationItemsList[i].ItemDescription);
                                        doc.SetCellValue("F" + m, data.Data.QuotationItemsList[i].SORItem);
                                        if (data.Data.QuotationItemsList[i].Qty > 0)
                                            doc.SetCellValue("H" + m, data.Data.QuotationItemsList[i].Qty);

                                        doc.SetCellValue("I" + m, data.Data.QuotationItemsList[i].Unit);
                                        if (data.Data.QuotationItemsList[i].Rate > 0)
                                            doc.SetCellValue("J" + m, data.Data.QuotationItemsList[i].Rate);

                                        if (data.Data.QuotationItemsList[i].ItemAmount > 0)
                                            doc.SetCellValue("K" + m, data.Data.QuotationItemsList[i].ItemAmount);

                                        TotalAmount += data.Data.QuotationItemsList[i].ItemAmount;
                                        tempIndex = m;
                                    }
                                    #endregion
                                }

                                BroughtForward = CarryForward = TotalAmount;
                                if (CurrentPage != NoOfPages)
                                {
                                    tempIndex += 1;
                                    doc.MergeWorksheetCells("I" + tempIndex, "J" + tempIndex);
                                    doc.SetCellValue("I" + tempIndex, "Carry Forward:");
                                    style = doc.GetCellStyle("K16");
                                    style.FormatCode = "#,##0.00";
                                    style.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
                                    style.Border.BottomBorder.Color = System.Drawing.Color.Black;
                                    style.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
                                    style.Border.TopBorder.Color = System.Drawing.Color.Black;
                                    doc.SetCellStyle("K" + tempIndex, style);
                                    doc.SetCellValue("K" + tempIndex, CarryForward);
                                }
                                #endregion

                                if (data.Data.IsQuotation == true)
                                {
                                    // (c) Update Work Completion Date
                                    tempStrn = "Work completion on " + data.Data.WorkCompletionDate.ToString("dd MMMM yyyy", CultureInfo.CreateSpecificCulture("en-US"));
                                    int tempIdx = WorkCompletionDateColumnNo - 1;
                                    //doc.SetCellValue("A" + tempIdx, "");

                                    if(CurrentPage == 1 && CurrentPage == NoOfPages)
                                        doc.MergeWorksheetCells("B" + WorkCompletionDateColumnNo, "E" + WorkCompletionDateColumnNo);

                                    doc.SetCellValue("B" + WorkCompletionDateColumnNo, tempStrn);
                                }
                                #endregion

                                int tempTotalAmtColumnNo = TotalAmountColumnNo + NoOfRowsToBeAdded;
                                if (NoOfPages == 1)
                                {
                                    style = doc.GetCellStyle("K16");
                                    style.FormatCode = "#,##0.00";
                                    style.Border.BottomBorder.BorderStyle = BorderStyleValues.Double;
                                    style.Border.BottomBorder.Color = System.Drawing.Color.Black;
                                    style.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
                                    style.Border.TopBorder.Color = System.Drawing.Color.Black;
                                    doc.SetCellStyle("K" + tempTotalAmtColumnNo, style);
                                    doc.SetCellValue("K" + tempTotalAmtColumnNo, TotalAmount);
                                }
                                else
                                {
                                    tempTotalAmtColumnNo += 2;

                                    style = doc.GetCellStyle("K16");
                                    style.FormatCode = "#,##0.00";
                                    style.Border.BottomBorder.BorderStyle = BorderStyleValues.Double;
                                    style.Border.BottomBorder.Color = System.Drawing.Color.Black;
                                    style.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
                                    style.Border.TopBorder.Color = System.Drawing.Color.Black;
                                    doc.SetCellStyle("K" + tempTotalAmtColumnNo, style);
                                    doc.SetCellValue("K" + tempTotalAmtColumnNo, TotalAmount);

                                    doc.MergeWorksheetCells("I" + tempTotalAmtColumnNo, "J" + tempTotalAmtColumnNo);
                                    doc.SetCellValue("I" + tempTotalAmtColumnNo, "Total Amount:");
                                }

                                doc.InsertPageBreak(EndOfPageRow, EndOfPageColumn);

                                // You don't have to set this part.
                                // But if you don't, you have to manually change to
                                // PageBreakPreview mode in Excel to see the page breaks.
                                //SLPageSettings ps = doc.GetPageSettings();
                                //ps.View = SheetViewValues.PageBreakPreview;
                                //doc.SetPageSettings(ps);
                            }
                            doc.Save();
                        }
                        #endregion
                    }
                    else if (data.Data.IsQuotation == false && data.Data.IsInvoice == true)
                    {
                        #region Invoice
                        using (SLDocument doc = new SLDocument(tempDestPath, newTabName))
                        {
                            #region Billing Info
                            doc.SetCellValue("C4", data.Data.client.ClientName);
                            doc.SetCellValue("C5", data.Data.client.Address1);
                            doc.SetCellValue("C6", data.Data.client.Address2);
                            doc.SetCellValue("C7", data.Data.client.Address3);
                            doc.SetCellValue("C9", data.Data.client.AttentionTo);

                            String[] InvNoSplit = data.Data.InvoiceNo.Split('-');
                            String tempInvNo = "";

                            for (int i = 1; i < InvNoSplit.Length; i++)
                            {
                                if (i != InvNoSplit.Length - 1)
                                    tempInvNo += InvNoSplit[i] + "-";
                                else
                                    tempInvNo += InvNoSplit[i];
                            }

                            String tempDNOrQuoNo = "";

                            if (data.Data.InvoiceItemList.Count == 1)
                            {
                                String[] QuoNoSplit = data.Data.Invoice.DNNo.Split('-');
                                for (int i = 1; i < QuoNoSplit.Length; i++)
                                {
                                    if (i != QuoNoSplit.Length - 1)
                                        tempDNOrQuoNo += QuoNoSplit[i] + "-";
                                    else
                                        tempDNOrQuoNo += QuoNoSplit[i];
                                }
                            }
                            else
                                tempDNOrQuoNo = data.Data.Invoice.DNNo;

                            doc.SetCellValue("J4", tempInvNo);
                            doc.SetCellValue("J5", tempDNOrQuoNo);
                            doc.SetCellValue("J6", data.Data.InvoiceDate);
                            doc.SetCellValue("J7", data.Data.VendorNo);
                            #endregion

                            #region RE - Debit Note No
                            String tStrn = "RE - " + tempDNOrQuoNo;
                            doc.SetCellValue("A14", tStrn);
                            #endregion

                            #region Agreed Total Sum As Per Attched Table - Debit Note No
                            tStrn = "Agreed Total Sum as per attached table " + tempDNOrQuoNo;

                            bool isFoundFM = fmRepsoitory.IsFMFound(data.Data.Invoice.DNNo.Split('-')[0]);

                            if (isFoundFM == true)
                                tStrn = "Agreed Total Sum as per quotation " + tempDNOrQuoNo;

                            doc.SetCellValue("A16", tStrn);
                            #endregion

                            #region 100% Upon Practical Completion
                            tStrn = "HK$ " + data.Data.Invoice.TotalAmount.ToString("#,##0.00");
                            doc.SetCellValue("J17", tStrn);
                            #endregion

                            #region Total Invoice Amount
                            tStrn = "HK$ " + data.Data.Invoice.TotalAmount.ToString("#,##0.00");
                            doc.SetCellValue("J19", tStrn);
                            #endregion

                            #region Total Invoice Amount Wording
                            tStrn = "** Say Hong Kong Dollars " + NumberUtil.changeCurrencyToWords((double)data.Data.Invoice.TotalAmount) + " **";
                            doc.SetCellValue("B23", tStrn);
                            #endregion

                            doc.Save();
                        }
                        #endregion

                        #region Invoice Items
                        using (SLDocument doc = new SLDocument(tempDestPath, "Attachment"))
                        {
                            InvoiceRepository invRepository = new InvoiceRepository();
                            QuotationRepository quoRepository = new QuotationRepository();
                            OrderRepository oRepository = new OrderRepository();

                            DataTable dt = new DataTable();
                            dt.Columns.Add("Input Date", typeof(string));
                            dt.Columns.Add("Vendor Name", typeof(string));
                            dt.Columns.Add("Location (refer to Business Unit)", typeof(string));
                            dt.Columns.Add("WO No.", typeof(string));
                            dt.Columns.Add("Quotation No.", typeof(string));
                            dt.Columns.Add("Invoice No.", typeof(string));
                            dt.Columns.Add("PO Amount (HK$)", typeof(decimal));
                            dt.Columns.Add("Received Date", typeof(string));
                            
                            foreach(InvoiceItemModel item in data.InvoiceItems)
                            {
                                InvoiceModel iModel = invRepository.GetInvoice(item.InvoiceNo);
                                QuotationModel qModel = quoRepository.GetQuotation(item.QuotationNo);
                                OrderModel oModel = oRepository.GetOrder(item.QuotationNo);

                                dt.Rows.Add(oModel.OrderCreationDate.ToString("dd-MMM-yyyy", CultureInfo.CreateSpecificCulture("en-US")),
                                    "Chung Kei", 
                                    oModel.Location,
                                    oModel.WorkOrderNo,
                                    item.QuotationNo,
                                    item.InvoiceNo,
                                    decimal.Round(qModel.TotalAmount, 2),
                                    "");
                            }

                            int iStartRowIndex = 1;
                            int iStartColumnIndex = 1;

                            doc.ImportDataTable(iStartRowIndex, iStartColumnIndex, dt, true);

                            // Set Column Style
                            SLStyle style = doc.CreateStyle();
                            style.FormatCode = "#,##0.00";
                            doc.SetColumnStyle(7, style);

                            // + 1 because the header row is included
                            // - 1 because it's a counting thing, because the start row is counted.
                            int iEndRowIndex = iStartRowIndex + dt.Rows.Count + 1 - 1;
                            // - 1 because it's a counting thing, because the start column is counted.
                            int iEndColumnIndex = iStartColumnIndex + dt.Columns.Count - 1;
                            SLTable table = doc.CreateTable(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex);
                            table.SetTableStyle(SLTableStyleTypeValues.Medium2);
                            table.HasTotalRow = true;
                            table.SetTotalRowLabel(6, "Total Amount (HK$)");
                            table.SetTotalRowFunction(7, SLTotalsRowFunctionValues.Sum);
                            doc.InsertTable(table);

                            doc.Save();
                        }
                        #endregion
                    }
                }
                else
                    return false;

                IsSuccess = true;
            }
            catch (Exception e)
            {
                IsSuccess = false;
            }
            return IsSuccess;
        }

        private static int GetStartIndex(ExcelModel data, int currentPageNo)
        {
            return currentPageNo * data.MaxNoOfRecords;
        }

        private static int GetEndIndex(ExcelModel data, int currentPageNo, int NoOfPages)
        {
            int ResultIndex = (currentPageNo * data.MaxNoOfRecords);

            if (currentPageNo == NoOfPages)
            {
                ResultIndex = data.Data.QuotationItemsList.Count;
                //if (data.Data.IsJLLOrderQuotation == true || data.Data.IsJLLOrderInvoice == true)
                //    ResultIndex = data.Data.QuotationList.Count;
                //else if (data.Data.IsJLLPOQuotation == true || data.Data.IsJLLPOInvoce == true)
                //    ResultIndex = data.Data.QuotationList.Count;
            }

            return ResultIndex;
        }
    }
}